﻿Imports TMySQL
Public Class TGridMySQLColumn
    Implements System.IDisposable
    Private Enum TMode As Integer
        mdChangeColumn = 0
        mdCreateTable = 1
    End Enum
    Private _Mode As TMode

    Private Enum TCol As Integer
        coColumnName = 0
        coColumnType = 1
        coDataLength = 2
        coCharacterSet = 3
        coDefault = 4
        coNullable = 5
        coCollation = 6
        coAttribute = 7
        coAutoIncrement = 8
        coComment = 9
        coCondition = 10
        coIndex = 10
    End Enum
    Private _Grid As DataGridView
    Private _Attributes As TMyAttributes
    Private _ColumnTypes As TMyColumnTypes
    Private _CharacterSets As TMyCharacterSets
    Private _Collations As TMyCollations
    Private _HasCondition As Boolean
    Private _Table As IHasColumns
    Private _MyQuery As New TMyQuery
    Private _SchemaName As String
    Private _TableName As String
    Private _Engine As String
    Private _Comment As String
    Private _Collation As String
    Private _Position As String
    Public Property Position() As String
        Get
            Return _Position
        End Get
        Set(ByVal value As String)
            _Position = value
        End Set
    End Property
    Public Property SchemaName() As String
        Get
            Return _SchemaName
        End Get
        Set(ByVal value As String)
            _SchemaName = value
        End Set
    End Property
    Public Property TableName() As String
        Get
            Return _TableName
        End Get
        Set(ByVal value As String)
            _TableName = value
        End Set
    End Property
    Public Property Engine() As String
        Get
            Return _Engine
        End Get
        Set(ByVal value As String)
            _Engine = value
        End Set
    End Property
    Public Property Comment() As String
        Get
            Return _Comment
        End Get
        Set(ByVal value As String)
            _Comment = value
        End Set
    End Property

    Public Property Collation() As String
        Get
            Return _Collation
        End Get
        Set(ByVal value As String)
            _Collation = value
        End Set
    End Property

    Public ReadOnly Property CharacterSet() As String
        Get
            Return Me.Collations.GetCharacterSetName(_Collation)
        End Get
    End Property

    Public Sub New(ByRef AGrid As DataGridView, ByVal ATable As IHasColumns, ByVal AHasCondition As Boolean)
        _MyQuery.Session = frmMain.Connection
        _Grid = AGrid
        _Table = ATable
        _Mode = TMode.mdChangeColumn
        _HasCondition = AHasCondition
    End Sub

    ''' <summary>
    ''' CREATE TABLE用
    ''' </summary>
    ''' <param name="AGrid"></param>
    ''' <remarks></remarks>
    Public Sub New(ByRef AGrid As DataGridView)
        _MyQuery.Session = frmMain.Connection
        _Grid = AGrid
        _Mode = TMode.mdCreateTable
        _HasCondition = False
    End Sub

    Public Property Attributes() As TMyAttributes
        Get
            If _Attributes Is Nothing Then
                Return frmMain.Attributes
            Else
                Return _Attributes
            End If
        End Get
        Set(ByVal value As TMySQL.TMyAttributes)
            _Attributes = value
        End Set
    End Property
    Public Property ColumnTypes() As TMyColumnTypes
        Get
            If _ColumnTypes Is Nothing Then
                Return frmMain.ColumnTypes
            Else
                Return _ColumnTypes
            End If
        End Get
        Set(ByVal value As TMyColumnTypes)
            _ColumnTypes = value
        End Set
    End Property
    Public Property CharacterSets() As TMyCharacterSets
        Get
            If _CharacterSets Is Nothing Then
                Return frmMain.CharacterSets
            Else
                Return _CharacterSets
            End If
        End Get
        Set(ByVal value As TMyCharacterSets)
            _CharacterSets = value
        End Set
    End Property
    Public Property Collations() As TMyCollations
        Get
            If _Collations Is Nothing Then
                Return frmMain.Collations
            Else
                Return _Collations
            End If
        End Get
        Set(ByVal value As TMyCollations)
            _Collations = value
        End Set
    End Property

    Public Sub InitColumns()
        _Grid.SuspendLayout()
        Try
            _Grid.Columns.Clear()
            _Grid.Columns.Add("colColumnName", "列名")
            _Grid.Columns.Add("colColumnType", "型")
            _Grid.Columns.Add("colDataLength", "長さ")
            _Grid.Columns.Add("colCharacterSet", "キャラクタセット")
            _Grid.Columns.Add("colDefault", "デフォルト")
            _Grid.Columns.Add("colNullable", "NULL")
            _Grid.Columns.Add("colCollation", "照合順序")
            _Grid.Columns.Add("colAttribute", "属性")
            _Grid.Columns.Add("colAutoIncrement", "AUTO_INCREMENT")
            _Grid.Columns.Add("colComment", "コメント")
            If _HasCondition Then
                _Grid.Columns.Add("colCondition", "条件")
            Else
                _Grid.Columns.Add("colIndex", "インデックス")
            End If
            InitColumnType()
            InitCharacterSet()
            InitNullable()
            InitCollation()
            InitAttribute()
            InitAutoIncrement()
            If Not _HasCondition Then
                InitIndex()
            End If
            InitColWidth()
        Finally
            _Grid.ResumeLayout()
        End Try
    End Sub

    Private Sub InitColWidth()
        _Grid.Columns(TCol.coColumnName).Width = 133
        _Grid.Columns(TCol.coColumnType).Width = 174
        _Grid.Columns(TCol.coDataLength).Width = 94
        _Grid.Columns(TCol.coCharacterSet).Width = 92
        _Grid.Columns(TCol.coDefault).Width = 80
        _Grid.Columns(TCol.coNullable).Width = 46
        _Grid.Columns(TCol.coCollation).Width = 141
        _Grid.Columns(TCol.coAttribute).Width = 94
        _Grid.Columns(TCol.coIndex).Width = 118
        _Grid.Columns(TCol.coAutoIncrement).Width = 118
        _Grid.Columns(TCol.coComment).Width = 180

    End Sub
    Private Sub InitAutoIncrement()
        Dim column As New DataGridViewCheckBoxColumn
        column.DataPropertyName = "colAutoIncrement"
        _Grid.Columns.Insert(_Grid.Columns("colAutoIncrement").Index, column)
        _Grid.Columns.Remove("colAutoIncrement")
        column.Name = "colAutoIncrement"
        column.HeaderText = "AUTO_INCREMENT"
    End Sub
    Private Sub InitNullable()
        Dim column As New DataGridViewCheckBoxColumn
        column.DataPropertyName = "colNullable"
        _Grid.Columns.Insert(_Grid.Columns("colNullable").Index, column)
        _Grid.Columns.Remove("colNullable")
        column.Name = "colNullable"
        column.HeaderText = "NULL"
    End Sub
    Private Sub InitColumnType()
        Dim column As New DataGridViewComboBoxColumn()
        For i As Integer = 0 To ColumnTypes.Count - 1
            column.Items.Add(ColumnTypes(i).ColumnTypeName)
        Next
        column.DataPropertyName = "colColumnType"
        _Grid.Columns.Insert(_Grid.Columns("colColumnType").Index, column)
        _Grid.Columns.Remove("colColumnType")
        column.Name = "colColumnType"
        column.HeaderText = "型"
    End Sub

    Private Sub InitIndex()
        Dim column As New DataGridViewComboBoxColumn()
        column.Items.Add(String.Empty)
        column.Items.Add("PRIMARY")
        column.Items.Add("UNIQUE")
        column.Items.Add("INDEX")
        column.Items.Add("FULLTEXT")

        column.DataPropertyName = "colIndex"
        _Grid.Columns.Insert(_Grid.Columns("colIndex").Index, column)
        _Grid.Columns.Remove("colIndex")
        column.Name = "colIndex"
        column.HeaderText = "インデックス"
    End Sub
    Private Sub InitCharacterSet()
        Dim column As New DataGridViewComboBoxColumn()
        column.Items.Add(String.Empty)
        For i As Integer = 0 To CharacterSets.Count - 1
            column.Items.Add(CharacterSets(i).CharacterSetName)
        Next
        column.DataPropertyName = "colCharacterSet"
        _Grid.Columns.Insert(_Grid.Columns("colCharacterSet").Index, column)
        _Grid.Columns.Remove("colCharacterSet")
        column.Name = "colCharacterSet"
        column.HeaderText = "キャラクタセット"
    End Sub
    Private Sub InitCollation()
        Dim column As New DataGridViewComboBoxColumn()
        column.Items.Add(String.Empty)
        For i As Integer = 0 To Collations.Count - 1
            column.Items.Add(Collations(i).CollationName)
        Next
        column.DataPropertyName = "colCollation"
        _Grid.Columns.Insert(_Grid.Columns("colCollation").Index, column)
        _Grid.Columns.Remove("colCollation")
        column.Name = "colCollation"
        column.HeaderText = "照合順序"

    End Sub
    Private Sub InitAttribute()
        Dim column As New DataGridViewComboBoxColumn()
        column.Items.Add(String.Empty)
        For i As Integer = 0 To Attributes.Count - 1
            column.Items.Add(Attributes(i).ExtraName)
        Next
        column.DataPropertyName = "colAttribute"
        _Grid.Columns.Insert(_Grid.Columns("colAttribute").Index, column)
        _Grid.Columns.Remove("colAttribute")
        column.Name = "colAttribute"
        column.HeaderText = "属性"
    End Sub

    Public Sub CreateTable()
        _MyQuery.Close()
        _MyQuery.SQL.Append(Me.CreateTableSQL)
        _MyQuery.Execute()
    End Sub
    Public Sub ValidateEntries()
        For Each row As DataGridViewRow In _Grid.Rows
            If String.IsNullOrEmpty(row.Cells(TCol.coColumnName).Value) Then
                Throw New Exception("列名を入力してください")
            End If
            If String.IsNullOrEmpty(row.Cells(TCol.coColumnType).Value) Then
                Throw New Exception("型を指定してください")
            End If
        Next
    End Sub
    Public ReadOnly Property CreateTableSQL() As String
        Get
            Dim sb As New System.Text.StringBuilder
            Dim isFirst As Boolean = True
            Dim col As String = String.Empty

            sb.Append("CREATE TABLE `").Append(Me.SchemaName).Append("`.`").Append(Me.TableName).AppendLine("` (")
            For i As Integer = 0 To _Grid.RowCount - 1
                If isFirst Then
                    sb.Append(" ").AppendLine(Me.AlterColumnText(i))
                    isFirst = False
                Else
                    sb.Append(",").AppendLine(Me.AlterColumnText(i))
                End If
            Next
            sb.AppendLine(")")
            sb.Append("ENGINE=").AppendLine(Me.Engine)
            sb.Append("DEFAULT CHARSET=").AppendLine(Me.CharacterSet)
            sb.Append("COMMENT='").Append(Me.Comment.Replace("'", "''")).Append("'")
            Return sb.ToString
        End Get
    End Property
    Public Sub AlterTableAdd()
        _MyQuery.Clear()
        _MyQuery.SQL.Append(Me.AlterTableAddSQL)
        _MyQuery.Execute()
    End Sub
    Public ReadOnly Property AlterTableAddSQL() As String
        Get
            Dim sb As New System.Text.StringBuilder
            Dim isFirst As Boolean = True
            Dim col As String = String.Empty

            sb.Append("ALTER TABLE `").Append(_Table.TableSchema).Append("`.`").Append(_Table.TableName).AppendLine("` ")
            For i As Integer = 0 To _Grid.RowCount - 1
                If Me.IsChanged(i) Then
                    If isFirst Then
                        sb.Append("ADD ").Append(Me.AlterColumnText(i))
                        If _Position.Equals("FIRST") Then
                            sb.Append(" FIRST")
                            col = _Table.Columns(i).ColumnName
                        ElseIf Not _Position.Equals("LAST") Then
                            sb.Append(" AFTER ").AppendLine(_Position)
                            col = _Table.Columns(i).ColumnName
                        End If
                        isFirst = False
                    Else
                        sb.Append(",ADD ").AppendLine(Me.AlterColumnText(i))
                        If _Position.Equals("FIRST") OrElse Not _Position.Equals("LAST") Then
                            sb.Append(" AFTER ").AppendLine(col)
                            col = _Table.Columns(i).ColumnName
                        End If
                    End If
                End If
            Next
            Return sb.ToString
        End Get
    End Property
    Public Sub AlterTableChange()
        _MyQuery.Clear()
        _MyQuery.SQL.Append(AlterTableChangeSQL)
        _MyQuery.Execute()
    End Sub
    Public ReadOnly Property AlterTableChangeSQL() As String
        Get
            Dim sb As New System.Text.StringBuilder
            Dim isFirst As Boolean = True

            sb.Append("ALTER TABLE `").Append(_Table.TableSchema).Append("`.`").Append(_Table.TableName).AppendLine("` ")
            For i As Integer = 0 To _Grid.RowCount - 1
                If Me.IsChanged(i) Then
                    If isFirst Then
                        sb.Append(" CHANGE `").Append(_Table.Columns(i).ColumnName).Append("` ").AppendLine(Me.AlterColumnText(i))
                        isFirst = False
                    Else
                        sb.Append(",CHANGE ").Append(_Table.Columns(i).ColumnName).Append("` ").AppendLine(Me.AlterColumnText(i))
                    End If
                End If
            Next
            Return sb.ToString
        End Get
    End Property

    Private Function AlterColumnText(ByVal ARow As Integer) As String
        Dim sb As New System.Text.StringBuilder

        sb.Append("`").Append(_Grid.Item(TCol.coColumnName, ARow).Value.ToString).Append("` ")
        sb.Append(_Grid.Item(TCol.coColumnType, ARow).Value.ToString)
        If _Grid.Item(TCol.coDataLength, ARow).Value IsNot Nothing AndAlso _Grid.Item(TCol.coDataLength, ARow).Value.ToString.Length > 0 Then
            sb.Append("(").Append(_Grid.Item(TCol.coDataLength, ARow).Value.ToString).Append(")")
        End If
        If Not String.IsNullOrEmpty(_Grid.Item(TCol.coAttribute, ARow).Value) Then
            sb.Append(_Grid.Item(TCol.coAttribute, ARow).Value.ToString)
        End If
        If Not String.IsNullOrEmpty(_Grid.Item(TCol.coCollation, ARow).Value) Then
            sb.Append(" CHARACTER SET ").Append(Collations.GetCharacterSetName(_Grid.Item(TCol.coCollation, ARow).Value.ToString))
            sb.Append(" COLLATE ").Append(_Grid.Item(TCol.coCollation, ARow).Value.ToString)
        End If
        If _Grid.Item(TCol.coNullable, ARow).Value IsNot Nothing AndAlso _Grid.Item(TCol.coNullable, ARow).Value = 1 Then
            sb.Append(" NULL")
        Else
            sb.Append(" NOT NULL")
        End If
        If _Grid.Item(TCol.coAutoIncrement, ARow).Value IsNot Nothing AndAlso _Grid.Item(TCol.coAutoIncrement, ARow).Value = 1 Then
            sb.Append(" AUTO_INCREMENT")
        End If
        If _Grid.Item(TCol.coDefault, ARow).Value IsNot Nothing AndAlso _Grid.Item(TCol.coDefault, ARow).Value.ToString.Length > 0 Then
            sb.Append(" DEFAULT ").Append(_Grid.Item(TCol.coDefault, ARow).Value.ToString)
        End If
        If _Grid.Item(TCol.coComment, ARow).Value IsNot Nothing AndAlso _Grid.Item(TCol.coComment, ARow).Value.ToString.Length > 0 Then
            sb.Append(" COMMENT '").Append(_Grid.Item(TCol.coComment, ARow).Value.ToString.Replace("'", "''")).Append("'")
        End If
        If Not _HasCondition Then
            If Not String.IsNullOrEmpty(_Grid.Item(TCol.coIndex, ARow).Value) Then
                If _Grid.Item(TCol.coIndex, ARow).Value.ToString.Equals("PRIMARY") Then
                    sb.Append(" PRIMARY KEY")
                Else
                    sb.Append(" ADD ").Append(_Grid.Item(TCol.coIndex, ARow).Value.ToString).Append("(`").Append(_Grid.Item(TCol.coColumnName, ARow).Value.ToString).Append("`)")
                End If
            End If
        End If
        Return sb.ToString
    End Function

    Private ReadOnly Property IsChanged(ByVal ARow As Integer) As Boolean
        Get

            If Not _Table.Columns(ARow).ColumnName.Equals(_Grid.Item(TCol.coColumnName, ARow).Value) Then
                Return True
            End If
            If Not _Table.Columns(ARow).DataType.ToUpper.Equals(_Grid.Item(TCol.coColumnType, ARow).Value) Then
                Return True
            End If
            If Not _Table.Columns(ARow).DataLength.Equals(_Grid.Item(TCol.coDataLength, ARow).Value) Then
                Return True
            End If
            If Not _Table.Columns(ARow).CharacterSetName.Equals(_Grid.Item(TCol.coCharacterSet, ARow).Value) Then
                Return True
            End If
            If Not _Table.Columns(ARow).ColumnDefault.Equals(_Grid.Item(TCol.coDefault, ARow).Value) Then
                Return True
            End If
            If Not _Table.Columns(ARow).IsNullable.Equals("Yes").Equals(_Grid.Item(TCol.coNullable, ARow).Value) Then
                Return True
            End If
            If Not _Table.Columns(ARow).CollationName.Equals(_Grid.Item(TCol.coCollation, ARow).Value) Then
                Return True
            End If
            If Not _Table.Columns(ARow).Attribute.ToUpper.Equals(_Grid.Item(TCol.coAttribute, ARow).Value) Then
                Return True
            End If
            If Not _Table.Columns(ARow).AutoIncrement.Equals(_Grid.Item(TCol.coAutoIncrement, ARow).Value) Then
                Return True
            End If
            If Not _Table.Columns(ARow).ColumnComment.Equals(_Grid.Item(TCol.coComment, ARow).Value) Then
                Return True
            End If
            Return False
        End Get
    End Property

    Private disposedValue As Boolean = False        ' 重複する呼び出しを検出するには

    ' IDisposable
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
                ' TODO: 他の状態を解放します (マネージ オブジェクト)。
            End If
            _MyQuery.Close()
            _MyQuery.Dispose()
            ' TODO: ユーザー独自の状態を解放します (アンマネージ オブジェクト)。
            ' TODO: 大きなフィールドを null に設定します。
        End If
        Me.disposedValue = True
    End Sub

#Region " IDisposable Support "
    ' このコードは、破棄可能なパターンを正しく実装できるように Visual Basic によって追加されました。
    Public Sub Dispose() Implements IDisposable.Dispose
        ' このコードを変更しないでください。クリーンアップ コードを上の Dispose(ByVal disposing As Boolean) に記述します。
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub
#End Region

End Class
